﻿Public Class clsGt

    Public Function getListById(sys_id As String) As DataTable
        Dim szSql As String = "select * from sf.b_gt where sys_id = ?"
        Return DBacs.ExecuteSql(szSql, -1, sys_id)
    End Function

    Public Function getGtList(startTime As String, endTime As String, ty As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select a.*,b.assist_marker as assist_marker_outsource,c.size as gjb_size,d.size as flb_size from sf.b_gt a "
        szSql &= "left join sf.b_pw_ck_in_details b on a.b_pw_ck_out_details_sys_id = b.sys_id "
        szSql &= "left join sf.t_mst_gjb c on a.type_value = c.mst_value and a.oper_type = '0' "
        szSql &= "left join sf.t_mst_flb d on a.type_value = d.mst_value and a.oper_type = '1' "
        szSql &= "where 1=1 "
        If startTime <> "" Then
            szSql &= " and a.update_time >= '" + startTime + "'"
        End If
        If endTime <> "" Then
            szSql &= " and a.update_time <= '" + endTime + "'"
        End If
        szSql &= " and a.oper_type = ? "
        Return DBacs.ExecuteSql(szSql, -1, ty)
    End Function

    Public Function getListByWorkreportStatus(ty As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select a.*,b.assist_marker as assist_marker_outsource,c.size as gjb_size,d.size as flb_size,e.mes_project_code,e.mes_project_name,e.material_code as material_code_pgd from sf.b_gt a "
        szSql &= "left join sf.b_pw_ck_in_details b on a.b_pw_ck_out_details_sys_id = b.sys_id "
        szSql &= "left join sf.t_mst_gjb c on a.type_value = c.mst_value and a.oper_type = '0' "
        szSql &= "left join sf.t_mst_flb d on a.type_value = d.mst_value and a.oper_type = '1' "
        szSql &= "left join sf.b_gt_pgd_mes e on a.b_gt_pgd_mes_sys_id = e.sys_id "
        szSql &= "where 1=1 and a.b_gt_pgd_mes_workreport_status <= 0 and a.oper_type = ? and a.b_gt_pgd_mes_sys_id is not null "
        Return DBacs.ExecuteSql(szSql, -1, ty)
    End Function

    Public Function getGt(oper_type As String, status As String) As DataTable
        Dim szSql As String = ""
        szSql &= "select a.*,b.assist_marker as assist_marker_outsource,c.size from sf.b_gt a "
        szSql &= "left join sf.b_pw_ck_in_details b on a.batch_no = b.batch_no "
        If oper_type.Equals("0") Then
            szSql &= "left join sf.t_mst_gjb c on a.type_value = c.mst_value "
        End If
        If oper_type.Equals("1") Then
            szSql &= "left join sf.t_mst_flb c on a.type_value = c.mst_value "
        End If
        szSql &= "where a.oper_type= ? and a.status in (" & status & ") "
        Return DBacs.ExecuteSql(szSql, -1, oper_type)
    End Function

    Public Function getCurrentGt(oper_type As String, status As String, procedure_no As String) As DataTable
        Dim szSql As String = ""
        szSql &= "select a.sys_id,a.oper_type,a.type_value,a.material_code,a.batch_no,a.picking_num,a.status,a.operator,a.update_time,a.assist_marker,a.assist_marker_jnj,b.ok_num,b.ng_num,b.procedure_no,c.size "
        szSql &= " from sf.b_gt a left join sf.b_gt_result_details b on a.sys_id = b.b_gt_sys_id "
        If oper_type.Equals("0") Then
            szSql &= "left join sf.t_mst_gjb c on a.material_code = c.material_code "
        End If
        If oper_type.Equals("1") Then
            szSql &= "left join sf.t_mst_flb c on a.material_code = c.material_code "
        End If
        szSql &= " where a.validity = ? and a.oper_type= ? and a.status = ? and b.procedure_no = ?"
        szSql &= " order by b.update_time desc"
        Return DBacs.ExecuteSql(szSql, -1, "1", oper_type, status, procedure_no)
    End Function

    Public Function updatePgdMesWorkreportStatus(sys_id As String, status As String) As Integer
        Dim sql As String = "UPDATE sf.b_gt SET b_gt_pgd_mes_workreport_status = ?, update_time= (SELECT CURRENT_TIMESTAMP(6)) WHERE sys_id = ? "
        Dim rtn As Integer = DBacs.ExecuteUpdateSql(sql, -1, status, sys_id)
        Return rtn
    End Function

    Public Function updateStatus(sys_id As String, status As String) As Integer
        Dim sql As String = "UPDATE sf.b_gt SET status = ?, update_time= (SELECT CURRENT_TIMESTAMP(6)) WHERE sys_id = ? "
        Dim rtn As Integer = DBacs.ExecuteUpdateSql(sql, -1, status, sys_id)
        Return rtn
    End Function

    Public Function update_assist_marker(sys_id As String, assist_marker As String) As Integer
        Dim sql As String = "UPDATE sf.b_gt SET assist_marker_jnj = ?, update_time= (SELECT CURRENT_TIMESTAMP(6)) WHERE sys_id = ? "
        Dim rtn As Integer = DBacs.ExecuteUpdateSql(sql, -1, assist_marker, sys_id)
        Return rtn
    End Function

    Public Function save(status As String, b_gt_pgd_mes_sys_id As String, sys_id As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        sql &= "UPDATE sf.b_gt SET status = ?, b_gt_pgd_mes_sys_id = ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) WHERE sys_id = ? "
        rtn = DBacs.ExecuteUpdateSql(sql, -1, status, b_gt_pgd_mes_sys_id, sys_id)
        Return rtn
    End Function

    Public Function saveOkNg(sys_id As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        sql &= "select * from sf.b_gt_result_details where b_gt_sys_id = ? order by sys_id desc"
        Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, sys_id)
        If dt.Rows.Count > 0 Then
            Dim okNum As Integer = Integer.Parse(dt.Rows(0)("ok_num").ToString())
            Dim ngNum As Integer = 0
            For index = 0 To dt.Rows.Count - 1
                ngNum += Integer.Parse(dt.Rows(index)("ng_num").ToString())
            Next
            sql = ""
            sql &= "UPDATE sf.b_gt SET ok_num = ?, ng_num = ? WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, okNum, ngNum, sys_id)
        End If
        Return rtn
    End Function

End Class
